{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         APPLY_NO APPLY_STS    APPLY_DT    DISTR_DT  SCORE_557  PFLAG_7D  \\\n",
      "0  20240487272847      COMP  2024-01-18  2024-01-19     623.77       0.0   \n",
      "\n",
      "   PFLAG_14D  PFLAG_30D  QYZXMODEL  HNDGMODEL  ...  ENTRODDAYAVGDEPO  \\\n",
      "0        0.0        0.0     637.37        NaN  ...            -999.0   \n",
      "\n",
      "   PERRODDBANKCUST  PERRODCURRACCTPROFIT  PERRODAGE  \\\n",
      "0              1.0                -999.0       32.0   \n",
      "\n",
      "   PERRODHOUSECOLLLOANBALANCE  PERRODOVERDUEAMT  PERRODOVERDUEDAY  \\\n",
      "0                      -999.0            -999.0            -999.0   \n",
      "\n",
      "   PERRODBASELIMIT  PERRODDAYAVGCURRBALANCE  PERRODBUSILOANCNT  \n",
      "0           -999.0                   -999.0                0.0  \n",
      "\n",
      "[1 rows x 48 columns]\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'LONGEST_OVDUE_DAYS'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[0;32m/opt/anaconda3/lib/python3.12/site-packages/pandas/core/indexes/base.py:3805\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   3804\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 3805\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n\u001b[1;32m   3806\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[0;32mindex.pyx:167\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mindex.pyx:196\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:7081\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:7089\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'LONGEST_OVDUE_DAYS'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[8], line 33\u001b[0m\n\u001b[1;32m     31\u001b[0m \u001b[38;5;66;03m# 创建目标变量Y\u001b[39;00m\n\u001b[1;32m     32\u001b[0m df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mY\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m2\u001b[39m  \u001b[38;5;66;03m# 默认值设为2\u001b[39;00m\n\u001b[0;32m---> 33\u001b[0m df\u001b[38;5;241m.\u001b[39mloc[df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mLONGEST_OVDUE_DAYS\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m30\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mY\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1\u001b[39m  \u001b[38;5;66;03m# 逾期>=30天的设为1\u001b[39;00m\n\u001b[1;32m     34\u001b[0m df\u001b[38;5;241m.\u001b[39mloc[df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mLONGEST_OVDUE_DAYS\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m<\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mY\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m   \u001b[38;5;66;03m# 逾期<=0天的设为0\u001b[39;00m\n\u001b[1;32m     36\u001b[0m \u001b[38;5;66;03m# 剔除Y=2的样本\u001b[39;00m\n",
      "File \u001b[0;32m/opt/anaconda3/lib/python3.12/site-packages/pandas/core/frame.py:4102\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   4100\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mnlevels \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[1;32m   4101\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_multilevel(key)\n\u001b[0;32m-> 4102\u001b[0m indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mget_loc(key)\n\u001b[1;32m   4103\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[1;32m   4104\u001b[0m     indexer \u001b[38;5;241m=\u001b[39m [indexer]\n",
      "File \u001b[0;32m/opt/anaconda3/lib/python3.12/site-packages/pandas/core/indexes/base.py:3812\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   3807\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(casted_key, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[1;32m   3808\u001b[0m         \u001b[38;5;28misinstance\u001b[39m(casted_key, abc\u001b[38;5;241m.\u001b[39mIterable)\n\u001b[1;32m   3809\u001b[0m         \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28many\u001b[39m(\u001b[38;5;28misinstance\u001b[39m(x, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m casted_key)\n\u001b[1;32m   3810\u001b[0m     ):\n\u001b[1;32m   3811\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m InvalidIndexError(key)\n\u001b[0;32m-> 3812\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[1;32m   3813\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[1;32m   3814\u001b[0m     \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[1;32m   3815\u001b[0m     \u001b[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[1;32m   3816\u001b[0m     \u001b[38;5;66;03m#  the TypeError.\u001b[39;00m\n\u001b[1;32m   3817\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_indexing_error(key)\n",
      "\u001b[0;31mKeyError\u001b[0m: 'LONGEST_OVDUE_DAYS'"
     ]
    }
   ],
   "source": [
    "#读取并合并csv文件part-00000-ada4b76e-d96c-4cb5-bb12-a60b47a276c8-c000.csv和part-00001-ada4b76e-d96c-4cb5-bb12-a60b47a276c8-c000.csv\n",
    "# 读取csv时制定GBK编码，请修正上述代码。\n",
    "import pandas as pd\n",
    "# df1 = pd.read_csv('part-00000-ada4b76e-d96c-4cb5-bb12-a60b47a276c8-c000.csv',sep='\\\\x7f\\\\x5e',engine='python')\n",
    "df = pd.read_csv('part-00001-ada4b76e-d96c-4cb5-bb12-a60b47a276c8-c000.csv',sep='\\\\x7f\\\\x5e',engine='python')\n",
    "# df = pd.concat([df1, df2], ignore_index=True)\n",
    "print(df.head(1))\n",
    "\n",
    "# 计算ks：针对上面的df，以SCORE，PERSONALCREDITSCORE，ENTERPRISECREDITSCORE，BANKPERSONALCREDITSCORE，BANKENTERPRISECREDITSCORE，INDUSTRYSCORE这些字段分别为X，LONGEST_OVDUE_DAYS>=30为1Y，计算ks\n",
    "# 定义计算KS的函数\n",
    "def calculate_ks(df, score_col, target_col='Y'):\n",
    "    # 按分数从高到低排序\n",
    "    df_sorted = df.sort_values(by=score_col, ascending=False)\n",
    "    \n",
    "    # 计算累积分布\n",
    "    total_pos = (df_sorted[target_col]==1).sum()\n",
    "    total_neg = (df_sorted[target_col]==0).sum()\n",
    "    \n",
    "    cum_pos = (df_sorted[target_col]==1).cumsum()\n",
    "    cum_neg = (df_sorted[target_col]==0).cumsum()\n",
    "    \n",
    "    # 计算累积占比\n",
    "    cum_pos_rate = cum_pos/total_pos\n",
    "    cum_neg_rate = cum_neg/total_neg\n",
    "    \n",
    "    # 计算KS值\n",
    "    ks = abs(cum_pos_rate - cum_neg_rate).max()\n",
    "    \n",
    "    return ks\n",
    "\n",
    "# 创建目标变量Y\n",
    "df['Y'] = 2  # 默认值设为2\n",
    "df.loc[df['LONGEST_OVDUE_DAYS'] >= 30, 'Y'] = 1  # 逾期>=30天的设为1\n",
    "df.loc[df['LONGEST_OVDUE_DAYS'] <= 0, 'Y'] = 0   # 逾期<=0天的设为0\n",
    "\n",
    "# 剔除Y=2的样本\n",
    "df = df[df['Y'] != 2]\n",
    "\n",
    "# 需要计算KS的评分字段列表\n",
    "score_cols = ['SCORE', 'PERSONALCREDITSCORE', 'ENTERPRISECREDITSCORE', \n",
    "              'BANKPERSONALCREDITSCORE', 'BANKENTERPRISECREDITSCORE', 'INDUSTRYSCORE']\n",
    "\n",
    "# 计算并输出每个评分的KS值\n",
    "for col in score_cols:\n",
    "    ks = calculate_ks(df, col)\n",
    "    print(f\"{col}的KS值为: {ks:.4f}\")\n",
    "    # 输出各类样本数量\n",
    "    print(f\"{col}评分的样本分布:\")\n",
    "    print(df['Y'].value_counts())\n",
    "    print(\"------------------------\")\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "检测到的编码：None\n"
     ]
    }
   ],
   "source": [
    "import chardet\n",
    "\n",
    "# 检测文件编码\n",
    "with open('part-00000-ada4b76e-d96c-4cb5-bb12-a60b47a276c8-c000.csv', 'rb') as file:\n",
    "    raw_data = file.read()\n",
    "    result = chardet.detect(raw_data)\n",
    "    print(f\"检测到的编码：{result['encoding']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
